package com.tsd.core.excel.impexcel;

import cn.afterturn.easypoi.util.PoiCellUtil;
import com.tsd.core.utils.*;
import com.tsd.core.utils.DateUtil;
import com.tsd.core.vo.HlpException;
import com.tsd.core.utils.HlpUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.util.*;

import static cn.afterturn.easypoi.util.PoiCellUtil.getCellValue;

/**
 * 导入工具类
 *
 * @author wudw
 */
public class ExcelFileUtil<T> {

    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    private static final String[] COLUMN_LETTER = new String[]{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"};

    public static void main(String[] args) {
        /*
         * ExcelFileUtil<RentInfo> efu = new ExcelFileUtil<RentInfo>(); String
         * filePath = "D:/temp/下D区缴费明细.xls"; ExcelConfigFile configFile = new
         * ExcelConfigFile(); try { configFile.init("RentInfo.xml");
         * ExcelObject<RentInfo> eo1 = efu.readExcelObject(filePath, configFile,
         * 0, RentInfo.class); efu.updateExcelFile(eo1, filePath, configFile, 0,
         * RentInfo.class); ExcelObject<RentInfo> eo =
         * efu.readExcelObject(filePath, configFile, 1, RentInfo.class);
         * efu.updateExcelFile(eo, filePath, configFile, 1, RentInfo.class); }
         * catch (Exception e) { e.printStackTrace(); }
         */
    }

    public static ExcelFileUtil<Map<String, Object>> getInstance() {
        return new ExcelFileUtil<>();
    }

    /**
     * 将 excel 中的字母列索引转换为数字列索引
     *
     * @param letterColIdx 字母列索引
     * @return rlt 返回字母列索引对应的数字索引 A -> 0  BA -> 52 CA -> 78
     */
    public static int transLetter2Num(String letterColIdx) throws Exception {
        int rlt = 0;
        letterColIdx = letterColIdx.toUpperCase();
        String[] sts = letterColIdx.split("");
        int length = sts.length;
        for (int i = 0; i < length; i++) {
            String st = sts[i];
            char[] cs = st.toCharArray();

            int idx = cs[0];

            // 如果字符不是 A-Z 之间的字符, 则抛错
            if (idx < 65 || idx > 90) {
                throw new HlpException(letterColIdx + " not between A and Z.");
            }

            idx -= 64;
            long num = (long) (idx * Math.pow(26, length - i - 1));
            rlt += num;
        }
        return --rlt;
    }

    public ExcelObject<T> readExcelObject(String filePath, ExcelConfigFile configFile, Class<T> cls) throws Exception {
        ExcelObject<T> eo;
        File dir = new File(filePath);
        if (!dir.exists()) {
            throw new Exception("文件不存在");
        }
        FileInputStream is = new FileInputStream(filePath);
        Workbook wb = this.getWorkbook(is, filePath);
        try {
            Sheet sheet = wb.getSheetAt(configFile.getSheetIndex());
            int lastRowIndex = sheet.getLastRowNum();
            eo = xls2List4Entity(wb, sheet, configFile.getDataStartRow(), lastRowIndex, configFile, cls);
            List<ExcelFileTitle> excelFileTitles = readTitle(sheet, configFile.getDataStartRow(), configFile);
            eo.setExcelFileTitles(excelFileTitles);
        } catch (Exception e) {
            if (e instanceof IOException) {
                throw new HlpException("文件读取错误，请确认文件是Excel格式并且没有加密");
            } else {
                throw e;
            }
        } finally {
            FileUtil.safeCloseInputStrem(is);
        }
        return eo;
    }

    public ExcelObject<Map<String, Object>> readExcelObject(String filePath, ExcelConfigFile configFile) throws Exception {
        ExcelObject<Map<String, Object>> eo;
        File dir = new File(filePath);
        if (!dir.exists()) {
            throw new Exception("文件不存在");
        }
        FileInputStream is = new FileInputStream(filePath);
        Workbook wb = this.getWorkbook(is, filePath);
        try {
            Sheet sheet = wb.getSheetAt(configFile.getSheetIndex());
            int lastRowIndex = sheet.getLastRowNum();
            eo = xls2List4Map(wb, sheet, configFile.getDataStartRow(), lastRowIndex, configFile);
            List<ExcelFileTitle> excelFileTitles = readTitle(sheet, configFile.getDataStartRow(), configFile);
            eo.setExcelFileTitles(excelFileTitles);
        } catch (Exception e) {
            if (e instanceof IOException) {
                throw new HlpException("文件读取错误，请确认文件是Excel格式并且没有加密");
            } else {
                throw e;
            }
        } finally {
            FileUtil.safeCloseInputStrem(is);
        }
        return eo;
    }

    public List<List<String>> readExcelData(String filePath, int sheetIndex) throws Exception {
        File dir = new File(filePath);
        if (!dir.exists()) {
            throw new Exception("文件不存在");
        }
        FileInputStream is = new FileInputStream(filePath);
        List<List<String>> datas = new ArrayList<List<String>>();
        Workbook wb = this.getWorkbook(is, filePath);
        try {
            Sheet sheet = wb.getSheetAt(sheetIndex);
            int lastRowIndex = sheet.getLastRowNum();
            int colNum = sheet.getRow(0).getPhysicalNumberOfCells();
            for (int i = 0; i <= lastRowIndex; i++) {
                List<String> rowData = readRow(sheet, i, colNum);
                datas.add(rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            FileUtil.safeCloseInputStrem(is);
        }
        return datas;
    }

    private Workbook getWorkbook(FileInputStream is, String filePath) throws Exception {
        return this.getWorkbook(is, filePath, null);
    }

    private Workbook getWorkbook(FileInputStream is, String filePath, ExcelConfigFile configFile) throws Exception {
        Workbook wb;
        try {
            // 设置最小解压比率以解决 Zip bomb 错误
            ZipSecureFile.setMinInflateRatio(-1.0d);
            if (configFile != null && !HlpUtils.isEmpty(configFile.getPassword())) {
                ZipSecureFile.setMinInflateRatio(0);
                return WorkbookFactory.create(is, configFile.getPassword());
            }
            if (filePath.toLowerCase().endsWith(".xls")) {
                wb = new HSSFWorkbook(is);
            } else if (filePath.toLowerCase().endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                throw new Exception("文件格式必须是xls或者xlsx");
            }
        } catch (Exception e) {
            throw new HlpException("文件读取错误，请确认文件是Excel格式并且没有加密");
        }
        return wb;
    }

    /**
     * 读取标题，只含列号、标题，缺少其他信息
     *
     * @param sheet
     * @param index
     * @param colNum
     * @return
     */
    private List<String> readRow(Sheet sheet, int index, int colNum) {
        Row row = sheet.getRow(index);
        List<String> list = new ArrayList<String>();
        for (int i = 0; i < colNum; i++) {
            String title = PoiExcelUtil.getCellValue(row, i);
            list.add(title);
        }
        return list;
    }

    /**
     * 读取标题，只含列号、标题，缺少其他信息
     *
     * @param sheet
     * @param dataStartRow
     * @param configFile
     * @return
     */
    private List<ExcelFileTitle> readTitle(Sheet sheet, int dataStartRow, ExcelConfigFile configFile) {
        Row row = sheet.getRow(dataStartRow - 1);
        List<ExcelFileTitle> list = new ArrayList<>();
        for (ExcelConfig ec : configFile.getItems()) {
            ExcelFileTitle e = new ExcelFileTitle();
            e.setFieldName(ec.getFieldName());
            String title = PoiExcelUtil.getCellValue(row, ec.getCellIndex());
            e.setTitle(title);
            e.setHide(ec.isHide());
            e.setDataType(ec.getFieldType());
            e.setColumnIndex(ec.getCellIndex());
            list.add(e);
        }
        return ListUtil.sorted(list, Comparator.comparing(ExcelFileTitle::getColumnIndex));
    }

    private ExcelObject<T> xls2List4Entity(Workbook wb, Sheet sheet, int firstRowIndex, int lastRowIndex, ExcelConfigFile configFile, Class<T> cls) {
        List<T> list = new ArrayList<>();
        StringBuilder checkResultBuf = new StringBuilder();
        Map<String, String> rowError = new LinkedHashMap<String, String>();
        List<Integer> emptyRows = new ArrayList<Integer>();
        List<Integer> rowHasmergedCellRows = new ArrayList<>();
        Map<String, String> mergedValue = getCellValuesInMergedRegion(wb, sheet, rowHasmergedCellRows);
        for (int i = firstRowIndex; i <= lastRowIndex; i++) {
            if (i > lastRowIndex - configFile.getLastRowSkipQty()) {
                continue;
            }
            Row row = sheet.getRow(i);
            boolean rowEmpty = true;
            for (int j = 0; j < configFile.getColumnCount(); j++) {
                // 先判断是否空行
                if (!HlpUtils.isEmpty(PoiExcelUtil.getCellValue(row, j))) {
                    rowEmpty = false;
                    break;
                }
            }
            if (rowEmpty) {
                emptyRows.add(i);
            } else {
                T t = null;
                try {
                    t = cls.newInstance();
                } catch (Exception e) {
                    rowError.put("" + i, ExceptionUtil.getStackTraceFromException(e));
                    continue;
                }
                try {
                    Map<String, Object> properties = this.readCellValue(wb, configFile, row, sheet, i, checkResultBuf, mergedValue, rowHasmergedCellRows);
                    BeanUtils.populate(t, properties);
                    list.add(t);
                    if (checkResultBuf.length() > 0) {
                        rowError.put("" + i, checkResultBuf.substring(0, checkResultBuf.length() - 1));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    rowError.put("" + i, ExceptionUtil.getStackTraceFromException(e));
                }
            }

        }
        ExcelObject<T> eo = new ExcelObject<T>();
        eo.setEmptyRows(emptyRows);
        eo.setList(list);
        eo.setRowError(rowError);
        return eo;
    }

    private ExcelObject<Map<String, Object>> xls2List4Map(Workbook wb, Sheet sheet, int firstRowIndex, int lastRowIndex,
                                                          ExcelConfigFile configFile) {
        List<Map<String, Object>> list = new ArrayList<>();
        StringBuilder checkResultBuf = new StringBuilder();
        Map<String, String> rowError = new LinkedHashMap<>();
        List<Integer> emptyRows = new ArrayList<>();
        List<Integer> rowHasmergedCellRows = new ArrayList<>();
        Map<String, String> mergedValue = getCellValuesInMergedRegion(wb, sheet, rowHasmergedCellRows);
//		System.out.println("mergedValue size:"+mergedValue.size());
//		System.out.println("rowHasmergedCellRows size:"+rowHasmergedCellRows.size());
        for (int i = firstRowIndex; i <= lastRowIndex; i++) {
            if (i > lastRowIndex - configFile.getLastRowSkipQty()) {
                continue;
            }
            Row row = sheet.getRow(i);
            boolean rowEmpty = true;
            if (row != null) {
                List<Object> rowRawValue = new ArrayList<>();
                for (int j = 0; j < configFile.getColumnCount(); j++) {
                    // 先判断是否空行
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    CellType cellType = cell.getCellType();
                    if (CellType.FORMULA == cellType) {
                        continue;
                    }
                    String val = PoiExcelUtil.getCellValue(row, j);
                    rowRawValue.add(val);
                    if (!HlpUtils.isEmpty(val)) {
                        rowEmpty = false;
                        break;
                    }
                }
            }
            if (rowEmpty) {
                if (!rowError.isEmpty()) {
                    rowError.put("" + i, "空行，如果是最后面的请删除");
                }
                emptyRows.add(i);
            } else {
                Map<String, Object> properties = this.readCellValue(wb, configFile, row, sheet, i, checkResultBuf, mergedValue, rowHasmergedCellRows);
                list.add(properties);
                if (checkResultBuf.length() > 0) {
                    rowError.put("" + i, checkResultBuf.substring(0, checkResultBuf.length() - 1));
                }
            }
        }
        ExcelObject<Map<String, Object>> eo = new ExcelObject<>();
        eo.setEmptyRows(emptyRows);
        eo.setList(list);
        eo.setRowError(rowError);
        return eo;
    }

    /**
     * 将合并单元格的值转换为map，同时更新有合并单元格的行列表
     * 对于公式的单元格，则得到最终结果
     *
     * @param wb
     * @param sheet
     * @param hasMergedRows
     * @return
     */
    private Map<String, String> getCellValuesInMergedRegion(Workbook wb, Sheet sheet, List<Integer> hasMergedRows) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Map<String, String> map = new HashMap<>();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            Row fRow = sheet.getRow(firstRow);
            Cell fCell = fRow.getCell(firstColumn);
            String value = PoiCellUtil.getCellValue(fCell);
            CellType cellType = fCell.getCellType();
            if (CellType.FORMULA == cellType) {
                CellValue cellValue = evaluator.evaluate(fCell);
                value = String.valueOf(cellValue.getNumberValue());
            } else if (CellType.NUMERIC == cellType) {
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(fCell)) {
                    Date date = fCell.getDateCellValue();
                    if (date != null) {
                        value = HlpUtils.date2String(date, HlpUtils.Format_Yyyy_MM_dd);
                    }
                }
            }
            if (!HlpUtils.isEmpty(value)) {
                for (int j = firstRow; j < lastRow + 1; j++) {
                    for (int k = firstColumn; k < lastColumn + 1; k++) {
                        map.put(j + "-" + k, value);
                    }
                    if (!hasMergedRows.contains(j)) {
                        hasMergedRows.add(j);
                    }
                }
            }
        }

        return map;
    }

    private Map<String, Object> readCellValue(Workbook wb, ExcelConfigFile configFile, Row row, Sheet sheet, int i, StringBuilder checkResultBuf,
                                              Map<String, String> mergedValue, List<Integer> rowHasmergedCellRows) {
        Map<String, Object> properties = new LinkedHashMap<>();
        checkResultBuf.setLength(0);
        for (ExcelConfig ec : configFile.getItems()) {
            int j = ec.getCellIndex();
            String mvalue = null;
            if (rowHasmergedCellRows.contains(i)) {
                mvalue = mergedValue.get(i + "-" + j);
            }
//			String columnName = ec.getCellIndex() < COLUMN_LETTER.length ? COLUMN_LETTER[ec.getCellIndex()] : ("" + ec.getCellIndex() + 1);
            String columnName = ec.getCellLetter();
            if (ExcelConfig.FIELD_TYPE_STRING.equalsIgnoreCase(ec.getFieldType())) {
                this.readStringCellValue(wb, row, ec, columnName, checkResultBuf, properties, mvalue);
            } else if (ExcelConfig.FIELD_TYPE_INT.equalsIgnoreCase(ec.getFieldType())) {
                this.readIntCellValue(wb, row, ec, columnName, checkResultBuf, properties, mvalue);
            } else if (ExcelConfig.FIELD_TYPE_DATE.equalsIgnoreCase(ec.getFieldType())) {
                this.readDateCellValue(row, ec, columnName, checkResultBuf, properties, mvalue);
            } else if (ExcelConfig.FIELD_TYPE_DOUBLE.equalsIgnoreCase(ec.getFieldType())) {
                this.readDoubleCellValue(wb, row, ec, columnName, checkResultBuf, properties, mvalue);
            }
        }
        return properties;
    }

    /**
     * @param row
     * @param ec
     * @param columnName
     * @param checkResultBuf
     * @param properties
     * @param settedValue    已经读取的值，主要是合并单元格的值
     */
    private void readStringCellValue(Workbook wb, Row row, ExcelConfig ec, String columnName, StringBuilder checkResultBuf,
                                     Map<String, Object> properties, String settedValue) {
        String value = null;
        Cell cell = row.getCell(ec.getCellIndex());
        if (!HlpUtils.isEmpty(settedValue)) {
            value = settedValue;
        } else {
            if (cell != null) {
                CellType cellType = cell.getCellType();
                if (CellType.NUMERIC == cellType) {
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = HlpUtils.date2String(date, HlpUtils.Format_Yyyy_MM_dd);
                        }
                    } else {
                        value = PoiExcelUtil.getNumericValue2String(row, ec.getCellIndex());
                    }
                } else if (CellType.FORMULA == cell.getCellType()) {
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                    CellValue cellValue = evaluator.evaluate(cell);
                    value = String.valueOf(cellValue.getNumberValue());
                } else {
                    value = getCellValue(cell);
                }
            }
        }
        if (!HlpUtils.isEmpty(value)) {
            int len = value.trim().length();
            if (len > ec.getLength()) {
                checkResultBuf.append(columnName).append("列的数据超长").append(len).append(">").append(ec.getLength()).append("\n");
            }
            properties.put(ec.getFieldName(), value);
        } else {
            if (ec.isMust()) {
                checkResultBuf.append(columnName).append("列的数据不能为空,");
            }
        }
    }

    private void readIntCellValue(Workbook wb, Row row, ExcelConfig ec, String columnName, StringBuilder checkResultBuf,
                                  Map<String, Object> properties, String settedValue) {
        String value = null;
        if (!HlpUtils.isEmpty(settedValue)) {
            value = settedValue;
        } else {
            Cell cell = row.getCell(ec.getCellIndex());
            if (cell != null) {
                if (CellType.FORMULA == cell.getCellType()) {
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                    CellValue cellValue = evaluator.evaluate(cell);
                    value = String.valueOf(cellValue.getNumberValue());
                } else {
                    value = getCellValue(cell);
                }
            }
        }
        if (!HlpUtils.isEmpty(value)) {
            if (StringUtil.isDouble(value)) {
                try {
                    int valueInt = (int) Math.round(Double.parseDouble(value));
                    properties.put(ec.getFieldName(), valueInt);
                } catch (Exception e) {
                    checkResultBuf.append(columnName).append("列的数据不是整数,");
                }
            } else {
                checkResultBuf.append(columnName).append("列的数据不是整数,");
            }
        } else {
            if (ec.isMust()) {
                checkResultBuf.append(columnName).append("列的数据不能为空,");
            }
        }
    }

    private void readDoubleCellValue(Workbook wb, Row row, ExcelConfig ec, String columnName, StringBuilder checkResultBuf,
                                     Map<String, Object> properties, String settedValue) {
        String value = null;
        if (!HlpUtils.isEmpty(settedValue)) {
            value = settedValue;
        } else {
            Cell cell = row.getCell(ec.getCellIndex());
            if (cell != null) {
                if (CellType.FORMULA == cell.getCellType()) {
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                    CellValue cellValue = evaluator.evaluate(cell);
                    value = String.valueOf(cellValue.getNumberValue());
                } else {
                    value = getCellValue(cell);
                }
            }
        }
        if (!HlpUtils.isEmpty(value)) {
            if (StringUtil.isDouble(value)) {
                try {
                    Double valueD = Double.parseDouble(value);
                    properties.put(ec.getFieldName(), valueD);
                } catch (Exception e) {
                    checkResultBuf.append(columnName).append("列的数据不是数字,");
                }
            } else {
                if (value.endsWith("%")) {
                    value = value.substring(0, value.length() - 1);
                    if (StringUtil.isDouble(value)) {
                        try {
                            Double valueD = Double.parseDouble(value);
                            valueD = DoubleUtil.div(valueD, 100D);
                            properties.put(ec.getFieldName(), valueD);
                        } catch (Exception e) {
                            checkResultBuf.append(columnName).append("列的数据不是数字,");
                        }
                    } else {
                        checkResultBuf.append(columnName).append("列的数据不是数字,");
                    }
                } else {
                    checkResultBuf.append(columnName).append("列的数据不是数字,");
                }
            }
        } else {
            if (ec.isMust()) {
                checkResultBuf.append(columnName).append("列的数据不能为空,");
            }
        }
    }

    private void readDateCellValue(Row row, ExcelConfig ec, String columnName, StringBuilder checkResultBuf, Map<String, Object> properties, String settedValue) {
        try {
            Date valueD = null;
            String valueStr = PoiExcelUtil.getCellValue(row, ec.getCellIndex());
            valueD = HlpUtils.isEmpty(valueStr) ? null : PoiExcelUtil.getDateCellValue(row, ec.getCellIndex());
            if (HlpUtils.isEmpty(valueD) && !HlpUtils.isEmpty(settedValue)) {
                if (StringUtil.isDouble(settedValue)) {
                    int val = (int) Double.parseDouble(settedValue);
                    valueD = DateUtil.excelDateInt2Date(val);
                } else {
                    valueD = HlpUtils.string2Date(settedValue, HlpUtils.Format_Yyyy_MM_dd);
                }
            }
            if (valueD != null) {
                properties.put(ec.getFieldName(), valueD);
            } else {
                if (ec.isMust()) {
                    checkResultBuf.append(columnName).append("列的数据不能为空,");
                }
            }
        } catch (Exception e) {
            checkResultBuf.append(columnName).append("列的数据").append(PoiExcelUtil.getCellValue(row, ec.getCellIndex())).append("数据错误，请检查是否正确的日期,");
        }
    }

    public void updateExcelFile4Config(ExcelObject<T> eo, String filePath, ExcelConfigFile configFile) throws Exception {
        if (eo.getRowError().isEmpty()) {
            return;
        }
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            Workbook wb = this.getWorkbook(fis, filePath);
            Font font1 = PoiExcelUtil.createFonts(wb, true, "宋体", false, (short) 240);
            font1.setColor(IndexedColors.RED.getIndex());

            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(font1);

            Sheet sheet = wb.getSheetAt(configFile.getSheetIndex());
            Set<String> errRows = eo.getRowError().keySet();
            int errColIndex = configFile.getErrColIndex();
            int rowCount = sheet.getLastRowNum();
            for (int i = configFile.getDataStartRow(); i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    PoiExcelUtil.createCell(row, errColIndex, "", cellStyle);
                }
            }
            for (String rowIndex : errRows) {
                String err = eo.getRowError().get(rowIndex);
                Row row = sheet.getRow(Integer.parseInt(rowIndex));
                if (row != null) {
                    if (!HlpUtils.isEmpty(err)) {
                        // PoiExcelUtil.createCell(wb, row, 17, oi.errorMsg,
                        // font1);
                        // 这个写法会报错：java.lang.IllegalStateException: The maximum
                        // number of cell styles was exceeded. You can define up
                        // to 4000 styles in a .xls workbook
                        // at
                        // org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1120)
                        PoiExcelUtil.createCell(row, errColIndex, err, cellStyle);
                    } else {
                        PoiExcelUtil.createCell(row, errColIndex, "", cellStyle);
                    }
                }
            }
            FileOutputStream os = null;
            try {
                os = new FileOutputStream(filePath);
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        } finally {
            FileUtil.safeCloseInputStrem(fis);
        }

    }

    public void updateExcelFile(String filePath, int sheetIndex, Map<String, String> errorMap) throws Exception {
        Workbook wb = null;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            if (filePath.toLowerCase().endsWith(".xls")) {
                wb = new HSSFWorkbook(fis);
            } else if (filePath.toLowerCase().endsWith(".xlsx")) {
                wb = new XSSFWorkbook(fis);
            }

            Font font1 = PoiExcelUtil.createFonts(wb, false, "宋体", false, (short) 240);

            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(font1);

            Sheet sheet = wb.getSheetAt(sheetIndex);
            int colNum = sheet.getRow(0).getPhysicalNumberOfCells();
            int errColIndex = colNum + 1;
            int rowCount = sheet.getLastRowNum();
            Set<String> errRows = errorMap.keySet();
            for (String rowIndex : errRows) {
                String err = errorMap.get(rowIndex);
                Row row = sheet.getRow(Integer.parseInt(rowIndex));
                if (row != null) {
                    if (!HlpUtils.isEmpty(err)) {
                        PoiExcelUtil.createCell(row, errColIndex, err, cellStyle);
                    } else {
                        PoiExcelUtil.createCell(row, errColIndex, "", cellStyle);
                    }
                }
            }
            FileOutputStream os = null;
            try {
                os = new FileOutputStream(filePath);
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        } finally {
            FileUtil.safeCloseInputStrem(fis);
        }

    }

    public void appendExcelFile(String filePath, int sheetIndex, int errColIndex, Map<String, String> errorMap) throws Exception {
        Workbook wb = null;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            if (filePath.toLowerCase().endsWith(".xls")) {
                wb = new HSSFWorkbook(fis);
            } else if (filePath.toLowerCase().endsWith(".xlsx")) {
                wb = new XSSFWorkbook(fis);
            }

            Font font1 = PoiExcelUtil.createFonts(wb, false, "宋体", false, (short) 240);

            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(font1);

            Sheet sheet = wb.getSheetAt(sheetIndex);
            int rowCount = sheet.getLastRowNum();
            Set<String> errRows = errorMap.keySet();
            for (String rowIndex : errRows) {
                String err = errorMap.get(rowIndex);
                int index = Integer.parseInt(rowIndex) + rowCount;
                Row row = sheet.getRow(index);
                if (row == null) {
                    row = sheet.createRow(index);
                }
                if (row != null) {
                    if (!HlpUtils.isEmpty(err)) {
                        PoiExcelUtil.createCell(row, errColIndex, err, cellStyle);
                    } else {
                        PoiExcelUtil.createCell(row, errColIndex, "", cellStyle);
                    }
                }
            }
            FileOutputStream os = null;
            try {
                os = new FileOutputStream(filePath);
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        } finally {
            FileUtil.safeCloseInputStrem(fis);
        }

    }

    /**
     * 根据EXCEL列名求其索引,例如：A-》0，AA-26
     *
     * @param cellStr
     * @return
     */
    public static int getCellNum(String cellStr) {
        char[] cellStrArray = cellStr.toUpperCase().toCharArray();
        int len = cellStrArray.length;
        int n = 0;
        for (int i = 0; i < len; i++) {
            n += (((int) cellStrArray[i]) - 65 + 1) * Math.pow(26, len - i - 1);
        }
        return n - 1;
    }
}
